﻿#include "home.h"
#include "ui_home.h"
#include <QFileDialog>
#include <QAxObject>
#include <QDebug>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlTableModel>
#include <QListWidget>
#include <QListWidgetItem>
#include <QHeaderView>
#include <QFont>

Home::Home(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Home)
{
    ui->setupUi(this);
    init();
}

Home::~Home()
{
    delete ui;
}

void Home::init()
{
    setWindowTitle("成绩记录系统");

    //tablewidget
    ui->tableWidget_0->setEditTriggers(QAbstractItemView::NoEditTriggers);//设置表格内容不可编辑
    ui->tableWidget_0->verticalHeader()->setHidden(false);//隐藏行号列
    ui->tableWidget_0->setHorizontalScrollBarPolicy(Qt::ScrollBarAlwaysOff);//设置水平滚动隐藏
    ui->tableWidget_0->setVerticalScrollBarPolicy(Qt::ScrollBarAlwaysOn);//设置竖直滚动
    ui->tableWidget_0->horizontalHeader()->setSectionResizeMode(QHeaderView::Fixed);//设置表头不可拖动
    ui->tableWidget_0->verticalHeader()->setSectionResizeMode(QHeaderView::Fixed);
    ui->tableWidget_0->setSelectionMode(QAbstractItemView::NoSelection);//设置不可选
    ui->tableWidget_0->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tableWidget_0->verticalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    //列设置
    ui->tableWidget_0->setRowCount(30);
    ui->tableWidget_0->setColumnCount(5);
    QStringList header;
    header << "学号" << "姓名" << "语文" << "数学" << "英语";
    ui->tableWidget_0->setHorizontalHeaderLabels(header);

    /*QTableWidgetItem *item = new QTableWidgetItem("1");
    item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
    ui->tableWidget_0->setItem(0, 0, item);
    item = new QTableWidgetItem("斌");
    item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
    ui->tableWidget_0->setItem(0, 1, item);
    item = new QTableWidgetItem("2");
    item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
    ui->tableWidget_0->setItem(0, 2, item);
    item = new QTableWidgetItem("3");
    item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
    ui->tableWidget_0->setItem(0, 3, item);
    item = new QTableWidgetItem("4");
    item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
    ui->tableWidget_0->setItem(0, 4, item);*/

    ui->tableWidget_0->horizontalHeader()->setStyleSheet(
    "QHeaderView::section{background-color:rgb(225,225,225);"
    "font:25pt '宋体';color: black;};");
    ui->tableWidget_0->verticalHeader()->setStyleSheet(
    "QHeaderView::section{background-color:rgb(225,225,225);"
    "color: black;};");

    //设置字体格式和大小
    QFont appFont;
    //appFont.setFamily("宋体");
    appFont.setPixelSize(30);
    this->setFont(appFont);
    QFont fLineEdit_0;
    fLineEdit_0.setPixelSize(50);
    ui->lineEdit_0->setFont(fLineEdit_0);
    //建立数据库连接
    createDateBaseConnection();
}

void Home::on_pushButton_0_clicked()
{
    QVariantList varRowContents = getExcelData();
    if (varRowContents.value(0) == false)
        return;
    loadExcelDataToDataBase(varRowContents);
    return;
}

QVariantList Home::getExcelData()
{
    QString filePath = QFileDialog::getOpenFileName(this, QStringLiteral("选择Excel文件"), "C:\\Users", QStringLiteral("Exel file(*.xls *.xlsx)"));
    if (filePath.isEmpty())
    {
        QVariantList falseReturn;
        falseReturn.append(false);
        return falseReturn;
    }
    QAxObject *excel = new QAxObject(this);
    excel->setControl("Excel.Application");//连接Excel控件
    excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
    excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
    //打开文件
    QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
    workbooks->dynamicCall("Open (const QString&)", filePath);
    QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
    //打开表
    QAxObject *worksheets = workbook->querySubObject("Sheets");//获取工作表集合
    QAxObject *worksheet = worksheets->querySubObject("Item(int)",1);//工作表1，即sheet1
    //获取表中的数据到QVariant中
    QAxObject *usedRange = worksheet->querySubObject("UsedRange");
    QVariant var = usedRange->dynamicCall("value");
    QVariantList varRowContents = var.toList();
    //删除，防止程序关闭后有excel进程残留
    excel->dynamicCall("Quit(void)");
    delete excel;
    return varRowContents;
}

void Home::loadExcelDataToDataBase(QVariantList varRowContents)
{
    bool dataBaseExist = QSqlDatabase::contains("sqlite1");
    if (dataBaseExist == false)
    {
        bool successCreateConnection = createDateBaseConnection();
        if (successCreateConnection == false)
            return;
    }
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    if (db.tables().contains("grades") == false)
    {
        bool successCreateTable = createTable();
        if (successCreateTable == false)
            return;
    }
    bool successInsertData = insertData(varRowContents);
    if (successInsertData == false)
        return;
    return;
}

bool Home::createDateBaseConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "sqlite1");
    db.setDatabaseName("grade.db");
    if (!db.open())
    {
        qDebug() << "Can't create connection with database!";
        return false;
    }
    return true;
}

bool Home::createTable()
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    QSqlQuery query(db);
    bool success = query.exec("create table grades(id int primary key,name varchar(5),"
                              "Chinese int,math int,English int);");
    if (success)
    {
        qDebug() << QObject::tr("数据库表创建成功！");
        return true;
    }
    else
    {
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText() << QString(QObject::tr("数据库表创建失败！"));
        return false;
    }
    return true;
}

bool Home::insertData(QVariantList allData)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    const int rows = allData.size();
    for (int i = 1; i < rows; i++)
    {
        QVariantList nowRow = allData[i].toList();//取一行，相当于数组
        query.prepare("insert into grades values(?, ?, ?, ?, ?)");
        const int cols = nowRow.size();
        for (int j = 0; j < cols; j++)
            query.bindValue(j, nowRow[j]);
        bool successInserSingleRow = query.exec();
        if (successInserSingleRow == false)
        {
            QSqlError lastError = query.lastError();
            qDebug() << lastError.driverText() << QString(QObject::tr("插入失败"));
            return false;
        }
    }
    qDebug() << "插入成功啦！";
    return true;
}

bool Home::queryById(QString id)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    //query.exec(QString("select * from grades"));
    query.exec(QString("select * from grades where id = %1;").arg(id));
    if (query.last())
    {
        qDebug() << query.at() + 1 << 'a' << endl;
    }//读取数据

    QSqlTableModel *tableModel = new QSqlTableModel(this, db);
    tableModel->setTable("grades");
    QSqlRecord rec = tableModel->record();
    int cols = rec.count();//获得字段数

    ui->tableWidget_0->clearContents();//清楚表格中已有内容

    query.first();
    query.previous();
    int row = 0;//表示当前行
    while (query.next())
    {
        QString record = "";
        for(int index = 0; index < cols; index++)
        {
            record = query.value(index).toString();
            QTableWidgetItem *item = new QTableWidgetItem(record);
            item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
            ui->tableWidget_0->setItem(row, index, item);
        }
        row++;
    }//将读取的数据显示出来
    return true;
}

void Home::on_pushButton_1_clicked()
{
    QString id_s = ui->lineEdit_0->text();
    queryById(id_s);
    return;
}
